﻿using System;
using System.Data.SqlServerCe;
using Dentiste.Domain.Entities;
using Dentiste.Domain.Interfaces;

namespace Dentiste.Domain.Providers
{
    internal class PatientProvider : IProvider<IPatient>
    {
        private readonly string _connectionString;

        #region Constructor

        public PatientProvider(string connectionString)
        {
            _connectionString = connectionString;
        }

        #endregion

        public DbSetCustom<IPatient> Select()
        {
            const string queryString = "SELECT * FROM Patients";
            var ret = new DbSetCustom<IPatient>(this);
            using (var connection = new SqlCeConnection(_connectionString))
            {
                var cmd = new SqlCeCommand(queryString, connection);
                connection.Open();
                SqlCeDataReader reader = cmd.ExecuteReader();
                while (reader != null && reader.Read())
                {
                    ret.Add(new Patient
                        {
                            Id = reader.GetInt32(0),
                            FirstName = reader.GetString(1),
                            LastName = reader.GetString(2)
                        });
                }
            }
            return ret;
        }

        public IPatient Select(string id)
        {
            const string queryString = "SELECT * FROM Patients where Id=?";
            IPatient p = null;
            using (var connection = new SqlCeConnection(_connectionString))
            {
                var cmd = new SqlCeCommand(queryString, connection);
                cmd.Parameters.AddWithValue("@Id", id);
                connection.Open();
                SqlCeDataReader reader = cmd.ExecuteReader();
                if (reader != null && reader.Read())
                {
                    p = new Patient
                    {
                        Id = reader.GetInt32(0),
                        FirstName = reader.GetString(1),
                        LastName = reader.GetString(2)
                    };
                }
            }
            return p;
        }

        public IPatient Select(string firstname, string lastname)
        {
            const string queryString = "SELECT * FROM Patients where FirstName=? AND LastName=?";
            IPatient p = null;
            using (var connection = new SqlCeConnection(_connectionString))
            {
                var cmd = new SqlCeCommand(queryString, connection);
                cmd.Parameters.AddWithValue("@FirstName", firstname);
                cmd.Parameters.AddWithValue("@LastName", lastname);

                connection.Open();

                SqlCeDataReader reader = cmd.ExecuteReader();
                if (reader != null && reader.Read())
                {
                    p = new Patient
                    {
                        Id = reader.GetInt32(0),
                        FirstName = reader.GetString(1),
                        LastName = reader.GetString(2)
                    };
                }
            }
            return p;
        }

        public IPatient SelectLastInserted()
        {
            const string queryString = "SELECT max(id) FROM Patients";
            using (var connection = new SqlCeConnection(_connectionString))
            {
                var cmd = new SqlCeCommand(queryString, connection);
                connection.Open();
                SqlCeDataReader reader = cmd.ExecuteReader();
                if (reader != null && reader.Read())
                {
                    string id = Convert.ToString(reader.GetInt32(0));
                    return Select(id);
                }
            }
            return null;
        }

        public IPatient Insert(IPatient entity)
        {
            int ret;
            const string queryString = "INSERT INTO Patients (FirstName, LastName) VALUES (?, ?)";
            using (var connection = new SqlCeConnection(_connectionString))
            {
                connection.Open();
                var cmd = new SqlCeCommand(queryString, connection);
                cmd.Parameters.AddWithValue("@FirstName", entity.FirstName);
                cmd.Parameters.AddWithValue("@LastName", entity.LastName);

                ret = cmd.ExecuteNonQuery();
            }
            if (ret == 1)
            {
                return SelectLastInserted();
            }
            return null;
        }

        public bool Update(IPatient entity)
        {
            int ret;
            const string queryString = "UPDATE Patients SET FirstName=?,LastName=? WHERE Id=?";
            using (var connection = new SqlCeConnection(_connectionString))
            {
                connection.Open();
                var cmd = new SqlCeCommand(queryString, connection);
                cmd.Parameters.AddWithValue("@FirstName", entity.FirstName);
                cmd.Parameters.AddWithValue("@LastName", entity.LastName);
                cmd.Parameters.AddWithValue("@Id", entity.Id);

                ret = cmd.ExecuteNonQuery();
            }
            return ret == 1;
        }

        public bool Delete(IPatient entity)
        {
            int ret;
            const string queryString = "DELETE FROM Patients WHERE Id=?";
            using (var connection = new SqlCeConnection(_connectionString))
            {
                connection.Open();
                var cmd = new SqlCeCommand(queryString, connection);
                cmd.Parameters.AddWithValue("@Id", entity.Id);

                ret = cmd.ExecuteNonQuery();
            }
            return ret == 1;
        }
    }
}